﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using CFM.DTO;
using CFM.DAL;
namespace CFM.API
{
    public class TableAPI
    {
        private DataHelper _helper = null;
        public TableAPI()
        {
            _helper = new DataHelper();
        }

        public List<Table> GetAll()
        {
            string sql = "select * FROM [TABLE] JOIN [TABLEGROUP] on [TABLE].[TABLEGROUPID]=TABLEGROUP.[TABLEGROUPID]";
            SqlDataReader dr = _helper.ExcuteDataReader(sql, null);
            return _helper.MapDataReaderToObject<Table>(dr);
        }
        public bool Create(Table table)
        {
            string sql = "insert into [table](tableid,tablename,tablegroupid) values(@tableid,@tablename,@tablegroupid)";
            SqlParameter[] prs =
            {
                new SqlParameter("@tableid",table.TableId),
                new SqlParameter("@tablename",table.TableName),
                new SqlParameter("@tablegroupid",table.TableGroupId)
            };
            return _helper.ExcuteNonQuery(sql, prs);
        }
        public bool Update(Table table)
        {
            string sql = "update [table] set tablename = @tablename,tablegroupid = @tablegroupid where tableid = @tableid";
            SqlParameter[] prs = 
            {
                new SqlParameter("@tableid",table.TableId),
                new SqlParameter("@tablename",table.TableName),
                new SqlParameter("@tablegroupid",table.TableGroupId)
            };
            return _helper.ExcuteNonQuery(sql, prs);
        }

        public bool Delete(string tableID)
        {
            string sql = "delete [table] where tableid = @tableid";
            SqlParameter[] prs = 
            {
                new SqlParameter("@tableid",tableID)
            };
            return _helper.ExcuteNonQuery(sql, prs);
        }
        public List<Table> GetTableFromGroupId(string groupId)
        {
            string sql = "select * FROM [TABLE] JOIN [TABLEGROUP] on [TABLE].[TABLEGROUPID]=TABLEGROUP.[TABLEGROUPID] where  [TABLE].[TABLEGROUPID] = @groupid ";
            SqlParameter[] prs = 
            {
                new SqlParameter("@groupid",groupId)
            };
            SqlDataReader dr =  _helper.ExcuteDataReader(sql, prs);
            return _helper.MapDataReaderToObject<Table>(dr);
        }
    }
}

